package com.focus.xyz.core.common.generator;

import com.focus.xyz.core.utils.Constants;
import com.focus.xyz.core.utils.prop.ConfigPropInfo;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

/**
 * Created by chenmeng on 2016/4/26.
 */
public class DataTask implements ITask {
    private static transient Logger log = LoggerFactory.getLogger(DataTask.class);

    public Map doTask(String dbType, String task,Map<String,String> outParasMap) throws Exception {
        String filePrefix = ConfigPropInfo.TEST_DATA_DIR + "tasks/";
        Map taskMap = new HashMap();
        ResultSet rs = null;
        boolean exec_flag = true;
        if (StringUtils.isNotBlank(task)) {
            //根据dbType获取数据库驱动,为空则默认为ORACLE
            IDriverSV driver = DriverFactory.getDriver(dbType);
            //将任务作为事务，有一条sql执行出错就回滚。
            //按顺序循环执行任务(","分割任务)
            String[] tNames = task.split(",");
            SQLOUT:
            for (int i = 0; i < tNames.length; i++) {
                String sqlName = tNames[i];
                //读取任务sql文件
                if (StringUtils.isNotBlank(sqlName)) {
                    String filePath = filePrefix + sqlName;
                    File sqlFile = new File(filePath);
                    if (sqlFile.exists()) {
                        BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF-8"));
                        String sqlLine = null;
                        int lineNo = 0;
                        while ((sqlLine = reader.readLine()) != null) {
                            lineNo = lineNo + 1;
                            if (StringUtils.isNotBlank(sqlLine)) {
                                //判断是否包含注释"--"行注释
                                if (sqlLine.startsWith("--")) {
                                    continue;
                                }
                                //判断是否包含注释"//"行注释
                                if (sqlLine.startsWith("//")) {
                                    continue;
                                }
                                //判断是否包含"/**/"行注释
                                if (sqlLine.startsWith("/*") && sqlLine.endsWith("*/")) {
                                    continue;
                                }
                                //判断是否包含"/**/"块注释
                                if (sqlLine.startsWith("/*") && !sqlLine.endsWith("*/")) {
                                    while ((sqlLine = reader.readLine()) != null) {
                                        lineNo = lineNo + 1;
                                        if (sqlLine.endsWith("*/")) {
                                            break;
                                        }
                                    }
                                    continue;
                                }
                                //判断是否是语句块，语句块是在一行
                                if (StringUtils.startsWithIgnoreCase(sqlLine, "begin") && StringUtils.endsWithIgnoreCase(sqlLine, "end;")) {
                                    //执行sql语句块。
                                    taskMap = driver.executeSqlBlock(sqlLine);
                                    if (Constants.FAIL.equals(taskMap.get(Constants.FLAG))) {
                                        logError(taskMap, sqlName, lineNo);
                                        exec_flag = false;
                                        break SQLOUT;
                                    }

                                }
                                //判断是否是语句块，语句块不在一行
                                if (StringUtils.startsWithIgnoreCase(sqlLine, "begin") && !StringUtils.endsWithIgnoreCase(sqlLine, "end;")) {
                                    StringBuilder sqlBlock = new StringBuilder();
                                    sqlBlock.append(sqlLine);
                                    while ((sqlLine = reader.readLine()) != null) {
                                        if (Constants.MYSQL.equals(dbType)) {
                                            sqlLine = transSqlStr(sqlLine);
                                        }
                                        lineNo = lineNo + 1;
                                        sqlBlock.append(sqlLine);
                                        if (StringUtils.endsWithIgnoreCase(sqlLine, "end;")) {
                                            //执行sql语句块
                                            taskMap = driver.executeSqlBlock(sqlBlock.toString());
                                            if (Constants.FAIL.equals(taskMap.get(Constants.FLAG))) {
                                                logError(taskMap, sqlName, lineNo);
                                                exec_flag = false;
                                                break SQLOUT;
                                            }
                                            break;
                                        }
                                    }
                                    continue;
                                } else {
                                    //sql文件中包含多条sql,用";"隔开
                                    if (sqlLine.trim().endsWith(";")) {
                                        sqlLine = sqlLine.substring(0, sqlLine.trim().length() - 1);
                                        if (Constants.MYSQL.equals(dbType)) {
                                            sqlLine = transSqlStr(sqlLine);
                                        }
                                    }
                                    //循环逐条执行sql语句
                                    String[] strs = sqlLine.split(";");
                                    for (int j = 0; j < strs.length; j++) {
                                        /*找到sql语句中的变量*/
                                        String[] outParams = getParamFromString(strs[j], "#{", "}");
                                        String[] sParams = getParamFromString(strs[j],"\"","\"");
                                        String newSql = setParamIntoSql(outParams,outParasMap,strs[j]);
                                        //查询参数start
                                        if(sParams.length != 0&&StringUtils.isNotBlank(sParams[0])){
                                            ResultSet rtn = driver.executeQuery2(newSql);
                                            setParams(rtn,outParasMap);
                                            newSql = setParamIntoSql(outParams,outParasMap,strs[j]);
                                        }
                                        //查询参数end
                                        taskMap = driver.execute(newSql);
                                        if (Constants.FAIL.equals(taskMap.get(Constants.FLAG))) {
                                            logError(taskMap, sqlName, lineNo);
                                            exec_flag = false;
                                            break SQLOUT;
                                        }
                                    }

                                }
                            }
                        }
                    } else {
                        exec_flag = false;
                        log.error("任务文件不存在.");
                    }
                } else {
                    exec_flag = false;
                    log.error("任务文件名为空。");
                }
            }
            //有一条sql出错，任务就回滚
            if (exec_flag) {
                taskMap.put(Constants.FLAG, Constants.SUCCESS);
                driver.commit();
                driver.closeConnection();
                log.debug("任务事务状态：commit");
            } else {
                driver.rollback();
                driver.closeConnection();
                log.debug("任务事务状态：rollback");
            }
        }
        return taskMap;
    }

    private String transSqlStr(String source) {
        String[] searchList = new String[]{"to_date(", "TO_DATE(", "'yyyy-mm-dd hh24:mi:ss'", "sysdate", "SYSDATE", "'yyyy-mm-dd'", "'dd-mm-yyyy'", "'DD-MM-YYYY'", "'mm-dd-yyyy'", "'MM-DD-YYYY'", "'dd-mm-yyyy hh24:mi:ss'", "'DD-MM-YYYY HH24:MI:SS'", "'DD-MM-YYYY HH12:MI:SS'", "'dd-mm-yyyy hh12:mi:ss'", "'mm-dd-yyyy hh24:mi:ss'", "'MM-DD-YYYY HH24:MI:SS'", "'mm-dd-yyyy hh12:mi:ss'", "'MM-DD-YYYY HH12:MI:SS'", "'yyyy-mm-dd hh12:mi:ss'", "'YYYY-MM-DD HH12:MI:SS'"};
        String[] replacementList = new String[]{"str_to_date(", "STR_TO_DATE(", "'%Y-%m-%d %H:%i:%s'", "sysdate()", "SYSDATE() ", "'%Y-%m-%d'", "'%d-%m-%Y'", "'%d-%m-%Y'", "'%m-%d-%Y'", "'%m-%d-%Y'", "'%d-%m-%Y %H:%i:%s'", "'%d-%m-%Y %H:%i:%s'", "'%d-%m-%Y %h:%i:%s'", "'%d-%m-%Y %h:%i:%s'", "'%m-%d-%Y %H:%i:%s'", "'%m-%d-%Y %H:%i:%s'", "'%m-%d-%Y %h:%i:%s'", "'%m-%d-%Y %h:%i:%s'", "'%Y-%m-%d %h:%i:%s'", "'%Y-%m-%d %h:%i:%s'"};
        String rtn = StringUtils.replaceEach(source, searchList, replacementList);
        return rtn;
    }

    private void logError(Map taskMap, String sqlName, int lineNo) {
        Object message = taskMap.get(Constants._EXCEPTION);
        taskMap.put(Constants._EXCEPTION, String.format("任务：%s的第%d行语句执行异常：%s", sqlName, lineNo, message));
        log.error(String.format("任务：%s的第%d行语句执行异常：%s", sqlName, lineNo, message));
    }



    /*
    * 转换sql中的变量
    * */
    public static String[] getParamFromString(String aSourceString, String aStartStr,
                                       String aEndStr) {
        aSourceString = aSourceString + aEndStr;
        String strSource = aSourceString;
        ArrayList strKey = new ArrayList();
        int iStartIndex = strSource.indexOf(aStartStr);
        int iStartLength = aStartStr.length();
        int iEndLength = aEndStr.length();
        String strTemp = "";
        strTemp = strSource.substring(iStartIndex + iStartLength,
                strSource.length());
        int iEndIndex = strTemp.indexOf(aEndStr)
                + strSource.substring(0, iStartIndex + iStartLength).length();
        if (iEndIndex == iStartIndex) {
            if (!strTemp.startsWith("="))
                strKey.add(strTemp);
        }
        while ((iStartIndex != -1) && (iEndIndex != -1)
                && (iStartIndex < iEndIndex)) {
            strTemp = strSource
                    .substring(iStartIndex + iStartLength, iEndIndex);
            if (!strTemp.startsWith("="))
                strKey.add(strTemp);
            strSource = strSource.substring(iEndIndex + iEndLength,
                    strSource.length());
            iStartIndex = strSource.indexOf(aStartStr);
            strTemp = strSource.substring(iStartIndex + iStartLength,
                    strSource.length());
            iEndIndex = strTemp.indexOf(aEndStr)
                    + strSource.substring(0, iStartIndex + iStartLength)
                    .length();
        }
        return (String[]) strKey.toArray(new String[0]);
    }

    /*替换sql中的变量*/
    public static String replaceParamString(String source, String s1, String s2) {
        int index = source.indexOf(s1);
        if (index == 0)
            return s2 + source.substring(s1.length());
        if (index > 0){
            return source.substring(0, index) + s2 + source.substring(index + s1.length());
        }
        return source;
    }

    public String setParamIntoSql(String[] parameterNames,Map paras,String sql) throws Exception{
        for(String pName:parameterNames){
            /*逐个替换sql中的变量*/
            if(paras!=null){
                Iterator<Map.Entry<String,String>> it=paras.entrySet().iterator();
                while(it.hasNext()){
                    Map.Entry<String,String> entry = it.next();
                    if(pName.equals(entry.getKey())){
                        sql = replaceParamString(sql,"#{"+pName+"}","'"+entry.getValue()+"'");
                        break;
                    }
                }
            }
        }
        return sql;
    }

    public String setSParamIntoSql(String[] params,Map paras,String sql) throws Exception{
        return null;
    }

    public static String replaceParamString(String source, String[] l, String aCode, String aStartStr, String aEndStr) {
        for (int i = 0; i < l.length; i++) {
            source = replaceParamString(source, aStartStr + l[i] + aEndStr, aCode);
        }
        return source;
    }

    public static String transParamString(String source,String aCode){
        String[] temp = source.split(aCode);
        String newStr ="";
        for(String s : temp){
            newStr = newStr + "'" + s + "',";
        }
        return newStr.substring(0,newStr.lastIndexOf(","));
    }

    public void setParams(ResultSet rs, Map sParamMap)
            throws SQLException {
        if (null != rs) {
            while (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    Object temp = null;
                    temp = sParamMap.get(rsmd.getColumnLabel(i));
                    if (null != temp) {
                        sParamMap.put(rsmd.getColumnLabel(i), temp + "," + rs.getString(i));
                    } else {
                        sParamMap.put(rsmd.getColumnLabel(i),rs.getString(i));
                    }
                }
            }
        }
    }

    public static void main(String[] args) {
        /*DataTask task = new DataTask();
        try {
            task.doTask(Constants.ORACLE, "DEL_PRD_SCOPE.sql,TEST.sql", Constants.TASK_FRONT);
        } catch (Exception e) {
            e.printStackTrace();
        }*/

        String[] parameterNames = getParamFromString("delete from cps.sub_member_login a where a.sub_login_name=#{param1};", "#{", "}");
        for(String s:parameterNames){
            System.out.println(s);
        }
    }
}
